package com.ten_4_4.hiring.comm.util;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.ResourceBundle;

import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.Validate;
import org.apache.log4j.Logger;

import com.ten_4_4.hiring.comm.PageModel;
import com.ten_4_4.hiring.company.po.ApplicationPo;
import com.ten_4_4.hiring.comm.util.DBUtil;
import com.ten_4_4.hiring.comm.util.Util;

public class DBUtil {
	
	private static final Logger LOG = Logger.getLogger(DBUtil.class);

    private static final ResourceBundle rb = ResourceBundle.getBundle("jdbc");
    private static final String DB_DRIVER = rb.getString("db.driver");
    private static final String DB_URL = rb.getString("db.url");
    private static final String DB_USERNAME = rb.getString("db.username");
    private static final String DB_PASSWORD = rb.getString("db.password");

    private DBUtil() throws IllegalAccessException {
        throw new IllegalAccessException();
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName(DB_DRIVER);

            connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
        } catch (ClassNotFoundException e) {
            // 注：在实际的项目开发中，一般异常信息都应该记录在日志文件中，或向管理员发送短信、邮件等信息！
            System.err.println("加载驱动失败，" + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("获取数据库连接失败！");
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 增删改
     * 
     * @param sql
     * @param params
     * @return
     */
    public static int executeUpdate(String sql, List<Object> paramList) {
        ValidateUtil.notBlank(sql, "参数sql不能为空：" + sql);

        int row = 0;
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            setParameter(paramList, preparedStatement);
            row = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.err.println("操作数据库失败，" + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.close(preparedStatement, connection);
        }
        return row;
    }

    public static int executeUpdate(String sql, Object... params) {
        return executeUpdate(sql, Arrays.asList(params));
    }

    /**
     * 查询：返回对象集合
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> List<T> executeQuery(Class<T> clazz, String sql, List<Object> paramList) {
        ValidateUtil.notNull(clazz, "参数clazz不能为空：" + clazz);
        ValidateUtil.notBlank(sql, "参数sql不能为空：" + sql);

        List<T> resultList = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParameter(paramList, preparedStatement);
            resultSet = preparedStatement.executeQuery();
            // 读取查询结果集的底层结构
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            // 得到查询了多少列信息
            int columns = resultSetMetaData.getColumnCount();
            // 循环所有的属性
            Field[] fields = clazz.getDeclaredFields();
            // 循环每条结果，并把结果封装成具体的实体对象，最后放到返回集合中
            while (resultSet.next()) {
                T obj = clazz.newInstance();
                // 对每列循环，把对应的值赋对obj的相应属性
                for (int i = 1; i <= columns; i++) {
                    String columnLabel = resultSetMetaData.getColumnLabel(i);
                    for (Field field : fields) {
                        // 允许操作私有属性
                        field.setAccessible(true);
                        // 获取当前属性的类型
                        Class<?> fieldClass = field.getType();
                        // 获取当前属性的名字
                        String fieldName = field.getName();
                        // 某个属性有大写，表示需要属性名与数据库列名对应
                        if (isContainsAlphaUpper(fieldName)) {
                            String columnName = toColumnName(fieldName);
                            if (columnName.equals(columnLabel)) {
                                setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
                                break;
                            }
                        } else {
                            // 表示属性名与数据库列名相同
                            if (fieldName.equals(columnLabel)) {
                                setFieldValue(resultSet, obj, columnLabel, field, fieldClass);
                                break;
                            }
                        }
                    }
                }
                resultList.add(obj);
            }
            return resultList;
        } catch (SQLException | InstantiationException | IllegalAccessException | IllegalArgumentException | SecurityException e) {
            throw new RuntimeException("数据查询失败");
        } finally {
            close(resultSet, preparedStatement, connection);
        }
    }

    public static <T> List<T> executeQuery(Class<T> clazz, String sql, Object... params) {
        return executeQuery(clazz, sql, Arrays.asList(params));
    }

    
    public static int getCount(String sql, Object...params) {
    	Connection conn = getConnection();
    	PreparedStatement ps = null;
    	ResultSet rs = null;
    	int count = 0;
    	try {
    		ps = conn.prepareStatement(sql);
    		setParameter(Arrays.asList(params), ps);
			rs = ps.executeQuery();
			if(rs.next())
				count = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, ps, conn);
		}
    	return count;
    }
    /**
     * 判断字符串里是否有大写字母
     * 
     * @param fieldName
     * @return
     */
    private static boolean isContainsAlphaUpper(String fieldName) {
        Validate.notBlank(fieldName, "参数fieldName不能为空：" + fieldName);

        for (int i = 0; i < fieldName.length(); i++) {
            if (CharUtils.isAsciiAlphaUpper(fieldName.charAt(i))) {
                return true;
            }
        }
        return false;
    }

    /**
     * Java属性 -> 数据库字段名：helloWorld -> hello_world
     * 
     * @param fieldName
     * @return
     */
    private static String toColumnName(String fieldName) {
        if (fieldName == null) {
            return "";
        }

        char[] chars = fieldName.toCharArray();
        StringBuilder sb = new StringBuilder();
        for (char c : chars) {
            if (CharUtils.isAsciiAlphaUpper(c)) {
                sb.append("_" + (char) (c + 32));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * 利用反射实现JavaBean的自动赋值
     * 
     * @param resultSet
     * @param obj
     * @param columnName
     * @param field
     * @param fieldClass
     * @throws IllegalAccessException
     * @throws SQLException
     */
    private static <T> void setFieldValue(ResultSet resultSet, T obj, String columnName, Field field, Class<?> fieldClass) throws IllegalAccessException, SQLException {
        // FIXME 根据业务扩展
        if (fieldClass.equals(int.class) || fieldClass.equals(Integer.class)) {
            field.set(obj, resultSet.getInt(columnName));
        } else if (fieldClass.equals(String.class)) {
            field.set(obj, resultSet.getString(columnName));
        } else if (fieldClass.equals(BigDecimal.class)) {
            field.set(obj, resultSet.getBigDecimal(columnName));
        } else if (fieldClass.equals(Date.class)) {
            field.set(obj, resultSet.getTimestamp(columnName));
        }
    }

    /**
     * 查询：返回单一对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> T getUniqueResult(Class<T> clazz, String sql, List<Object> paramList) {
        ValidateUtil.notNull(clazz, "参数clazz不能为空：" + clazz);
        ValidateUtil.notBlank(sql, "参数sql不能为空：" + sql);

        List<T> list = executeQuery(clazz, sql, paramList);
        if (list.isEmpty()) {
            return null;
        } else {
            return list.get(0);
        }
    }

    /**
     * 查询：返回单一对象
     * 
     * @param clazz
     * @param sql
     * @param paramList
     * @return
     */
    public static <T> T getUniqueResult(Class<T> clazz, String sql, Object... params) {
        return getUniqueResult(clazz, sql, Arrays.asList(params));
    }

    /**
     * 参数设置
     * 
     * @param paramList
     * @param preparedStatement
     * @throws SQLException
     */
    public static void setParameter(List<Object> paramList, PreparedStatement preparedStatement) throws SQLException {
        ValidateUtil.notNull(preparedStatement, "参数preparedStatement不能为空：" + preparedStatement);

        if (paramList != null) {
            for (int i = 0; i < paramList.size(); i++) {
                preparedStatement.setObject(i + 1, paramList.get(i));
            }
        }
    }

    /**
     * 关闭数据库资源
     */
    public static void close(ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    public static void close(PreparedStatement preparedStatement) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库资源释放失败");
        }
    }

    public static void close(PreparedStatement preparedStatement, Connection connection) {
        close(preparedStatement);
        close(connection);
    }

    public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        close(resultSet);
        close(preparedStatement, connection);
    }
    
    /**
	 * 增、删、改的数据库资源释放
	 * 
	 * @param connection
	 * @param preparedStatement
	 */
	public static void close(Connection connection,
			PreparedStatement preparedStatement) {
		close(preparedStatement);
		close(connection);
	}

	/**
	 * 增、删、改、查的数据库资源释放
	 * 
	 * @param connection
	 * @param preparedStatement
	 */
	public static void close(Connection connection,
			PreparedStatement preparedStatement, ResultSet resultSet) {
		close(resultSet);
		close(preparedStatement);
		close(connection);
	}
	
	/**
	 * 查询：返回分页对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> PageModel<T> executeQueryByPage(Class<T> clazz,
			int pageNo, int pageSize, String sql, List<Object> paramList) {
		List<T> list = executeQuery(clazz, sql, paramList);

		String countSql = "SELECT COUNT(*)"
				+ sql.substring(sql.indexOf("FROM"), sql.indexOf("ORDER BY"));
		int count = getCount(countSql, paramList);

		return new PageModel<T>(list, pageNo, pageSize, count);
	}

	/**
	 * 查询：返回分页对象
	 * 
	 * @param clazz
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static <T> PageModel<T> executeQueryByPage(Class<T> clazz,
			int pageNo, int pageSize, String sql, Object... params) {
		return executeQueryByPage(clazz, pageNo, pageSize, sql,
				Arrays.asList(params));
	}
	
	/**
	 * 统计总记录数
	 * 
	 * @param sql
	 * @param paramList
	 * @return
	 */
	public static int getCount(String sql, List<Object> paramList) {
		Validate.notNull(sql);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			List<Object> subList = paramList.subList(0, paramList.size() - 2);
			DBUtil.setParameter(subList, preparedStatement);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			Util.e(LOG, "分页时，查询总记录数失败!", e);
			throw new RuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}


}